package com.dong.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import com.dong.dao.UserDAO;
import com.dong.util.db.Database;
import com.dong.vo.User;


public class UserDAOImpl implements UserDAO {
	
	private Connection connection;

	public UserDAOImpl() {
		try {
			Database.getConnection();
		}catch(SQLException sqle) {
			sqle.printStackTrace();
		}
	}
	
	//添加用户，抛出异常，让调用者去扑捉异常
	public void addUser(User user) throws Exception {
		PreparedStatement ps = null;
		try {
			String sqlStr = "insert into user(uid,password,realName,gender,email,tel,question,answer,validateCode)values(?,?,?,?,?,?,?,?,?)";
			ps = connection.prepareStatement(sqlStr);
			ps.setString(1, user.getUid());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getRealName());
			ps.setInt(4, user.getGender());
			ps.setString(5, user.getEmail());
			ps.setString(6, user.getTel());
			ps.setString(7, user.getQuestion());
			ps.setString(8, user.getAnswer());
			ps.setString(9, user.getValidateCode());
			ps.executeUpdate();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			throw sqle;
		} finally {
			closeStatement(ps);
		}
	}

	//删除指定UID用户
	public void deleteUser(String uid)throws Exception {
		PreparedStatement ps = null;
		try {
			String sqlStr = "delete from user where uid=?";
			ps = connection.prepareStatement(sqlStr);
			ps.setString(1, uid);
			ps.executeUpdate();
			
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			throw sqle;
		} finally {
			closeStatement(ps);
		}
	}

	
	//获取指定UID用户
	public User getUser(String uid) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		User user = null;
		try {
			String sqlStr = "select * from user where uid = ?";
			ps = connection.prepareStatement(sqlStr);
			ps.setString(1, uid);
			rs = ps.executeQuery();			
			if(rs.next()) {		
				user = new User();
				user.setUid(rs.getString("uid"));
				user.setPassword(rs.getString("password"));
				user.setRealName(rs.getString("realName"));
				user.setEmail(rs.getString("email"));
				user.setGender(rs.getInt("gender"));
				user.setTel(rs.getString("tel"));
				user.setQuestion(rs.getString("question"));
				user.setAnswer(rs.getString("answer"));
				user.setValidateCode(rs.getString("validateCode"));
				user.setLoginNum(rs.getInt("loginNum"));
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			throw sqle;
		} finally {
			closeResultSet(rs);
			closeStatement(ps);
		}
		
		return user;
	}

	public List<User> lisetAllUser() throws Exception {
		List<User> list = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			String sqlStr = "select * from user";
			ps = connection.prepareStatement(sqlStr);
			rs = ps.executeQuery();
			while(rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUid(rs.getString("uid"));
				user.setPassword(rs.getString("password"));
				user.setGender(rs.getInt("gender"));
				user.setEmail(rs.getString("email"));
				user.setTel(rs.getString("tel"));
				user.setQuestion(rs.getString("question"));
				user.setAnswer(rs.getString("answer"));
				user.setRealName(rs.getString("realName"));
				user.setValidateCode(rs.getString("validateCode"));
				user.setLoginNum(rs.getInt("loginNum"));
				list.add(user);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			throw sqle;
		} finally {
			closeResultSet(rs);
			closeStatement(ps);
		}
		return list;
	}

	
	//更新用户
	public void updateUser(User user) throws Exception {
		PreparedStatement ps = null;
		
		try {
			String sqlStr = "update user set password=?,realName=?,gender=?,email=?,tel=?,question=?,answer=?,validateCode=?,loginNum=? where uid=?";
			ps = connection.prepareStatement(sqlStr);
			ps.setString(1, user.getPassword());
			ps.setString(2, user.getRealName());
			ps.setInt(3, user.getGender());
			ps.setString(4, user.getEmail());
			ps.setString(5, user.getTel());
			ps.setString(6, user.getQuestion());
			ps.setString(7, user.getAnswer());
			ps.setString(8, user.getValidateCode());
			ps.setInt(9, user.getLoginNum());
			ps.setString(10,user.getUid());
			
			ps.executeUpdate();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			throw sqle;
		} finally {
			closeStatement(ps);
		}
	}

	
	//Connection的get/set方法
	public void setConnection(Connection connection) throws Exception {
		this.connection = connection;
	}	
	public Connection getConnection() throws Exception {
		return connection;
	}
	
	//关闭Statement
	public static void closeStatement(Statement st) {
		if(st != null) {
			try {
				st.close();
				st = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	//关闭ResultSet
	public static void closeResultSet(ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
				rs = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
